using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace produccion.clases
{

    public class CLSDETPPCP:ObservableObject
    {
    
        #region Metodos basicos tabla

        MDOgeneral mod = new MDOgeneral();

        //Consulta las referencias de un pedido y sus acumulados
        public DataTable listaRefPedido() 
        {
            try 
            {
                string query = "select codcli_dpc,nroped_dpc,codref_dpc,(select nomref_ref from maeref where codref_ref=codref_dpc) as nomref_dpc, (select case when b.tiplin_lin <> 'C' then 50 when b.tiplin_lin = 'C' and b.genero_lin = 'F' then 50 when b.tiplin_lin = 'C' and b.genero_lin = 'M' then 60 else 50 end as Total from maeref a inner join maelin b on a.codlin_ref=b.codlin_lin where a.codref_ref = codref_dpc) as total, (select nomcol_col from maecol where codcol_col=codcol_dpc) as nomcol_dpc, codcol_dpc, sum(canped_dpc) - sum(candes_dpc) as canped_dpc, sum(candes_dpc) as candes_dpc, sum(canped_dpc) - sum(candes_dpc)-sum(canent_dpc) as canpen_dpc,(select  sum(canprg_dop) as canprg_dop from encordp,detordp where nroord_dop=nroord_eop and codref_dop=codref_eop and codcol_dop=codcol_eop and nroped_eop=nroped_dpc and codcli_eop=codcli_dpc and codref_eop=codref_dpc and codcol_eop=codcol_dpc group by nroped_eop,codref_eop,codcol_eop) as canprg_dpc, sum(CANENT_DPC) as CANENT from detppcp where codcli_dpc='" + CODCLI_DPC + "' and nroped_dpc=" + NROPED_DPC + " group by codcli_dpc,nroped_dpc,codref_dpc,codcol_dpc order by nomref_dpc";
                return mod.consulta(query);    
            }
            catch (Exception ex) { throw (ex); }
        }

        public DataTable listPedTalla() 
        {
            try 
            {
                string query = "select CODTAL_PRO,CODTAL_DPC,CANPED_DPC,CANPRG_DPC,CANPEN_DPC from (select * from MAEPRO  where CODREF_PRO='" + CODREF_DPC + "' and CODCOL_PRO='" + CODCOL_DPC + "') left  outer join (select CODTAL_DPC,CANPED_DPC,CODCLI_DPC,NROPED_DPC,CODREF_DPC,CODCOL_DPC,(select coalesce(sum(CANPRG_DOP) ,0) from ENCORDP,DETORDP where NROORD_DOP=NROORD_EOP and CODREF_DOP=CODREF_EOP and CODCOL_EOP=CODCOL_DOP and CODTAL_DOP=CODTAL_DPC and CODCLI_EOP=CODCLI_DPC and NROPED_EOP=NROPED_DPC and CODREF_EOP=CODREF_DPC and CODCOL_EOP=CODCOL_DPC) as CANPRG_DPC, CANPED_DPC-(select coalesce(sum(CANPRG_DOP) ,0) from ENCORDP,DETORDP where NROORD_DOP=NROORD_EOP and CODREF_DOP=CODREF_EOP and CODCOL_EOP=CODCOL_DOP and CODTAL_DOP=CODTAL_DPC and CODCLI_EOP=CODCLI_DPC and NROPED_EOP=NROPED_DPC and CODREF_EOP=CODREF_DPC and CODCOL_EOP=CODCOL_DPC) as CANPEN_DPC from DETPPCP where CODCLI_DPC='" + CODCLI_DPC + "' and NROPED_DPC=" + NROPED_DPC + " and CODREF_DPC='" + CODREF_DPC + "' and CODCOL_DPC='" + CODCOL_DPC + "') on CODREF_PRO=CODREF_DPC and CODCOL_PRO=CODCOL_DPC and CODTAL_PRO=CODTAL_DPC order by CODTAL_PRO";
                return mod.consulta(query);
            }
            catch (Exception ex) { throw ex; }
        }

        //Lista referencias por color para un pedido
        public DataTable lisDetPedCol() 
        { 
            try 
            {
                string query = "select valor_tar,((sum(canped_dpc)-sum(candes_dpc))*valor_tar) as total,codcli_dpc,nroped_dpc,codref_dpc,codcol_dpc,(select nomcol_col from maecol where codcol_col=codcol_dpc) as nomcol,nomref_ref,sum(canped_dpc) as canped_dpc, sum(canent_dpc) as canent_dpc, sum(candes_dpc) as candes_dpc, (sum(canent_dpc)-sum(canped_dpc)-sum(candes_dpc)) as demas, (sum(canped_dpc)-sum(canent_dpc)-sum(candes_dpc)) as canpen from detppcp,maeref,maetarp,maeclip  where (codref_ref=codref_dpc) and (codlis_tar=codlis_cli  and codcli_cli=codcli_dpc) and (codref_tar=codref_dpc and codcol_tar=codcol_dpc and codtal_tar=codtal_dpc) and (codcli_dpc='" + CODCLI_DPC + "' and nroped_dpc=" + NROPED_DPC + ") group by codcli_dpc,nroped_dpc,codref_dpc,codcol_dpc,codref_dpc,nomref_ref,valor_tar";
                return mod.consulta(query);
            }
            catch (Exception ex) { throw ex; }
        }

        //Lista tallas por referencia color para un pedido
        public DataTable lisDetPedTal() 
        {
            try
            {
                string query = "select codtal_pro,codcli_dpc,nroped_dpc,codref_dpc, codcol_dpc,codtal_dpc,canped_dpc,canent_dpc,candes_dpc,canpen from maepro left outer join (select codcol_dpc,codcli_dpc,nroped_dpc,codref_dpc,codtal_dpc,canped_dpc,canent_dpc,candes_dpc,(canped_dpc-canent_dpc-candes_dpc) as canpen from detppcp where codcli_dpc='"+CODCLI_DPC+"' and nroped_dpc="+NROPED_DPC+" and codref_dpc='"+CODREF_DPC+"' and codcol_dpc='"+CODCOL_DPC+"') on codref_pro=codref_dpc and codcol_pro=codcol_dpc and codtal_pro=codtal_dpc where codref_pro='"+CODREF_DPC+"' and codcol_pro='"+CODCOL_DPC+"'";
                return mod.consulta(query);
            }
            catch (Exception ex) { throw ex; }
        }
    
        //Consulta el pedido de una referencia en color y talla definido
        public void consultar()
        {
            try
            {
                string query = "select CODCLI_DPC,NROPED_DPC,CODREF_DPC,CODCOL_DPC,CODTAL_DPC,CANPED_DPC,CANENT_DPC,CANDES_DPC,PREPRO_DPC  from DETPPCP where NROPED_DPC=" + NROPED_DPC + " and CODCLI_DPC='" + CODCLI_DPC + "' and CODCOL_DPC='" + CODCOL_DPC + "' and CODTAL_DPC='" + CODTAL_DPC + "'";
                DataTable val = mod.consulta(query);
                if (val.Rows.Count > 0)
                {
                   CODCLI_DPC= val.Rows[0]["CODCLI_DPC"].ToString();
                   NROPED_DPC =Convert.ToInt32(val.Rows[0]["NROPED_DPC"]);
                   CODREF_DPC= val.Rows[0]["CODREF_DPC"].ToString();
                   CODCOL_DPC= val.Rows[0]["CODCOL_DPC"].ToString();
                   CODTAL_DPC= val.Rows[0]["CODTAL_DPC"].ToString();
                   CANPED_DPC =Convert.ToInt32(val.Rows[0]["CANPED_DPC"]);
                   CANENT_DPC =Convert.ToInt32( val.Rows[0]["CANENT_DPC"]);
                   CANDES_DPC =Convert.ToInt32( val.Rows[0]["CANDES_DPC"]);
                   PREPRO_DPC = Convert.ToDouble(val.Rows[0]["PREPRO_DPC"]);
                   EXISTE = true;
                   CAMBIO = false;
                }
                else
                {
                    EXISTE = false;
                }
            }
            catch (Exception e) { throw e; }
        }

        public int actualizar()
        {
            try
            {
                string query = "update DETPPCP set CANPED_DPC=" + CANPED_DPC + ",CANENT_DPC=" + CANENT_DPC + ",CANDES_DPC=" + CANDES_DPC + ",PREPRO_DPC=" + PREPRO_DPC + " where NROPED_DPC=" + NROPED_DPC + " and CODCLI_DPC='" + CODCLI_DPC + "' and CODCOL_DPC='" + CODCOL_DPC + "' and CODTAL_DPC='" + CODTAL_DPC + "'";
                return mod.comando(query);
            }
            catch (Exception e) { throw e; }
        }

        public void DescartarInsertar(string Ref, string Col)
        {
            try
            {
                DataTable dt = new DataTable();
                double Can=0;
                string query = "select codcli_dpc, nroped_dpc, codref_dpc, codcol_dpc, codtal_dpc, canped_dpc, canent_dpc, candes_dpc, prepro_dpc from detppcp where codcli_dpc='" + CODCLI_DPC + "' and nroped_dpc=" + NROPED_DPC + " and codref_dpc = '" + CODREF_DPC + "' and codcol_dpc = '" + CODCOL_DPC +"'";
                dt = mod.consulta(query);
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    query = "update detppcp set candes_dpc=candes_dpc + (canped_dpc - canent_dpc - candes_dpc) where codcli_dpc='" + CODCLI_DPC + "' and nroped_dpc=" + NROPED_DPC + " and codref_dpc='" + CODREF_DPC + "' and codcol_dpc='" + CODCOL_DPC + "' and codtal_dpc='" + dt.Rows[i][4].ToString() + "'";
                    mod.comando(query);
                    Can=System.Convert.ToDouble(dt.Rows[i][5].ToString()) - System.Convert.ToDouble(dt.Rows[i][6].ToString()) - System.Convert.ToDouble(dt.Rows[i][7].ToString());
                    if (Existe(CODCLI_DPC, NROPED_DPC, Ref, Col, dt.Rows[i][4].ToString()) == false)
                    {
                        query = "insert into detppcp (codcli_dpc, nroped_dpc, codref_dpc, codcol_dpc, codtal_dpc, canped_dpc, canent_dpc, candes_dpc, prepro_dpc) values ('" + CODCLI_DPC + "'," + NROPED_DPC + ",'" + Ref + "','" + Col + "','" + dt.Rows[i][4].ToString() + "'," + Can + ",0,0,0)";
                    }
                    else
                    {
                        query = "update detppcp set canped_dpc = canped_dpc + " + Can + " where codcli_dpc='" + CODCLI_DPC + "' and nroped_dpc=" + NROPED_DPC + " and codref_dpc='" + Ref + "' and codcol_dpc='" + Col + "' and codtal_dpc='" + dt.Rows[i][4].ToString() + "'";
                    }
                    mod.comando(query);
                }
            }
            catch (Exception ex) { throw ex; }
        }

        public int DescartarTodo()
        {
            try
            {
                string query = "update detppcp set candes_dpc = candes_dpc + (canped_dpc - canent_dpc - candes_dpc) where codcli_dpc='" + CODCLI_DPC + "' and nroped_dpc=" + NROPED_DPC + " and codref_dpc='" + CODREF_DPC + "' and codcol_dpc='" + CODCOL_DPC + "'";
                return mod.comando(query);
            }
            catch (Exception ex) { throw ex; }
        }

        public int Descartar()
        {
            try
            {
                string query = "update DETPPCP set CANDES_DPC=" + CANDES_DPC + " where NROPED_DPC=" + NROPED_DPC + " and CODCLI_DPC='" + CODCLI_DPC + "' and CODCOL_DPC='" + CODCOL_DPC + "' and CODTAL_DPC='" + CODTAL_DPC + "'";
                return mod.comando(query);
            }
            catch (Exception e) { throw e; }
        }

        public int insertar()
        {
            try
            {
                string query = "insert into DETPPCP (CODCLI_DPC,NROPED_DPC,CODREF_DPC,CODCOL_DPC,CODTAL_DPC,CANPED_DPC,CANENT_DPC,CANDES_DPC,PREPRO_DPC) values ('" + CODCLI_DPC + "'," + NROPED_DPC + ",'" + CODREF_DPC + "','" + CODCOL_DPC + "','" + CODTAL_DPC + "'," + CANPED_DPC + "," + CANENT_DPC + "," + CANDES_DPC + "," + PREPRO_DPC + ")";
                return mod.comando(query);
            }
            catch (Exception e) { throw e; }
        }

        public Boolean existe() 
        {
            string query="select  coalesce(count(CODCLI_DPC),0) from DETPPCP where (CODCLI_DPC='"+CODCLI_DPC+"') and (NROPED_DPC="+NROPED_DPC+") and (CODREF_DPC='"+CODREF_DPC+"') and (CODCOL_DPC='"+CODCOL_DPC+"') and (CODTAL_DPC='"+CODTAL_DPC+"')";
            DataTable val = mod.consulta(query);
            if (val.Rows[0][0].ToString().Equals("0"))
            {
                return false;
            }
            return true;
        }
    
        public DataTable buscar() 
        {
            string query="select CODCLI_DPC as ,NROPED_DPC as ,CODREF_DPC as ,CODCOL_DPC as ,CODTAL_DPC as ,CANPED_DPC as ,CANENT_DPC as ,CANDES_DPC as ,PREPRO_DPC as  from  DETPPCP where ";
            if(CODCLI_DPC!="")
            {
                query=query+"UPPER(CODCLI_DPC) like '%"+CODCLI_DPC.ToUpper()+"%' and ";
            }

            if(CODREF_DPC!="")
            {
                query=query+"UPPER(CODREF_DPC) like '%"+CODREF_DPC.ToUpper()+"%' and ";
            }

            if(CODCOL_DPC!="")
            {
                query=query+"UPPER(CODCOL_DPC) like '%"+CODCOL_DPC.ToUpper()+"%' and ";
            }

            if(CODTAL_DPC!="")
            {
                query=query+"UPPER(CODTAL_DPC) like '%"+CODTAL_DPC.ToUpper()+"%' and ";
            }

            query=query.Remove(query.Length - 4).Trim();
  
            return mod.consulta(query);
        }

        public Boolean Existe(string Cli, long Ped, string Ref, string Col, string Tal)
        {
            try
            {
                string query;
                if (String.IsNullOrEmpty(Tal) == true)
                {
                    query = "select codref_dpc from detppcp where codcli_dpc='" + Cli + "' and nroped_dpc=" + Ped + " and codref_dpc='" + Ref + "' and codcol_dpc='" + Col + "'";
                }
                else
                {
                    query = "select codref_dpc from detppcp where codcli_dpc='" + Cli + "' and nroped_dpc=" + Ped + " and codref_dpc='" + Ref + "' and codcol_dpc='" + Col + "' and codtal_dpc = '" + Tal + "'";
                }
                DataTable val = mod.consulta(query);
                if (val.Rows.Count > 0)
                {
                    return true;
                }
                return false;
            }
            catch (Exception ex) { throw ex; }
        }

        public double CantidadPedida(string codcli, long Ped, string Ref, string Col, string Tal)
        {
            try
            {
                string query = "select canped_dpc from detppcp where codcli_dpc='" + codcli + "' and nroped_dpc=" + Ped + " and codref_dpc='" + Ref + "' and codcol_dpc='" + Col + "' and codtal_dpc='" + Tal + "'";
                DataTable dt = mod.consulta(query);
                if (dt.Rows.Count > 0)
                {
                    return System.Convert.ToDouble(dt.Rows[0][0].ToString());
                }
                return 0;
            }
            catch (Exception ex) { throw ex; }
        }

        public int actPedido(List<CLSDETPPCP> lisdetped)
        {
            try
            {
                MDOgeneral.transaccion = MDOgeneral.conexion.BeginTransaction();
                for (int i = 0; i <= lisdetped.Count - 1; i++)
                {
                    lisdetped[i].actuDesca();
                }

                MDOgeneral.transaccion.Commit();
                return 1;
            }
            catch (Exception ex)  { MDOgeneral.transaccion.Rollback();  throw ex; }
        }

        public int actuDesca()
        {
            try
            {
                string query = "update DETPPCP set CANDES_DPC=" + CANDES_DPC +" where NROPED_DPC=" + NROPED_DPC + " and CODCLI_DPC='" + CODCLI_DPC + "' and CODCOL_DPC='" + CODCOL_DPC + "' and CODTAL_DPC='" + CODTAL_DPC + "'";
                return mod.comando(query);
            }
            catch (Exception e) { throw e; }
        }

        public DataTable PedidosCambiarDescartar()
        {
            try
            {
                string query = "select nroped_epc as Pedido,codcli_epc as Codigo,nomcli_cli as Cliente,fecped_epc as Fecha, sum(canped_dpc - canent_dpc - candes_dpc) as Pendiente from Encppcp inner join maeclip on codcli_epc=codcli_cli inner join detppcp on nroped_epc=nroped_dpc and codcli_epc=codcli_dpc where estped_epc='P' and canped_dpc - canent_dpc - candes_dpc > 0  and  codref_dpc='" + CODREF_DPC + "' AND codcol_dpc='" + CODCOL_DPC + "' group by nroped_epc,codcli_epc,nomcli_cli,fecped_epc order by nroped_epc, codcli_epc";
                DataTable dt = mod.consulta(query);
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region  Propiedades tabla DETPPCP

        private string _CODCLI_DPC;
        public string CODCLI_DPC
        {
            get { return _CODCLI_DPC;}
            set { _CODCLI_DPC=value; RaisePropertyChangedEvent("CODCLI_DPC"); }
        }

        private int _NROPED_DPC;
        public int NROPED_DPC
        {
            get { return _NROPED_DPC;}
            set { _NROPED_DPC=value; RaisePropertyChangedEvent("NROPED_DPC"); }
        }

        private string _CODREF_DPC;
        public string CODREF_DPC
        {
            get { return _CODREF_DPC;}
            set { _CODREF_DPC=value; RaisePropertyChangedEvent("CODREF_DPC"); }
        }

        private string _CODCOL_DPC;
        public string CODCOL_DPC
        {
            get { return _CODCOL_DPC;}
            set { _CODCOL_DPC=value; RaisePropertyChangedEvent("CODCOL_DPC"); }
        }

        private string _CODTAL_DPC;
        public string CODTAL_DPC
        {
            get { return _CODTAL_DPC;}
            set { _CODTAL_DPC=value; RaisePropertyChangedEvent("CODTAL_DPC"); }
        }

        private int _CANPED_DPC;
        public int CANPED_DPC
        {
            get { return _CANPED_DPC;}
            set 
            {
                if (_CANPED_DPC != value) { CAMBIO = true; } 
                _CANPED_DPC=value;
                RaisePropertyChangedEvent("CANPED_DPC");
            }
        }

        private int _CANENT_DPC;
        public int CANENT_DPC
        {
            get { return _CANENT_DPC;}
            set 
            {
                if (_CANENT_DPC != value) { CAMBIO = true; } 
                _CANENT_DPC=value;
                RaisePropertyChangedEvent("CANENT_DPC");
            }
        }

        private int _CANDES_DPC;
        public int CANDES_DPC{
            get { return _CANDES_DPC;}
            set 
            {
                if (_CANDES_DPC != value) { CAMBIO = true; } 
                _CANDES_DPC=value;
                RaisePropertyChangedEvent("CANDES_DPC");
            }
        }

        private double _PREPRO_DPC;
        public double PREPRO_DPC{
            get { return _PREPRO_DPC;}
            set 
            {
                if (_PREPRO_DPC != value) { CAMBIO = true; } 
                _PREPRO_DPC=value;
                RaisePropertyChangedEvent("PREPRO_DPC");
            }
        }

        private Boolean _CAMBIO = false;
        public Boolean CAMBIO
        {
            get { return _CAMBIO; }
            set { _CAMBIO = value; RaisePropertyChangedEvent("CAMBIO"); }
        }

        private Boolean _EXISTE = false;
        public Boolean EXISTE
        {
            get { return _EXISTE; }
            set { _EXISTE = value; RaisePropertyChangedEvent("EXISTE"); }
        }

        #endregion

    }
}
